﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Objects;
using System.Data.Linq;
using System.Data.Linq.SqlClient;
using HKEA.CMS.Core.IDAL.Tbl;
using HKEA.CMS.Core.Models;
using HKEA.CMS.Core.Utility;
using HKEA.CMS.Core.DALFactory;
using System.Data.Objects.DataClasses;
using iMaker.Library;

using System.Linq.Dynamic;

namespace HKEA.CMS.Core.DAL.Tbl
{
    public class tblCustomerDAL:ItblCustomer
    {
        HKEA_CMSEntities ctx;
        tblCustomer entCustomer;
        public tblCustomerDAL()
        {
            ctx = DataAccess.GetModelInstance();
        }

        public static tblCustomerDAL GetInstance()
        {
            return new tblCustomerDAL();
        }

        public List<CustomerListENT> GetCustomerDetailList(ref PageParam param, string strPropertyName, string strOrderBy)
        {
            HKEA_CMSEntities Models = DataAccess.GetModelInstance();
            #region 以实体对象形式返回结果集
            //var ret = from customer in Models.tblCustomers
            //          join cust_ContactPerson in Models.tblCust_ContactPerson
            //          on customer.CustomerUID equals cust_ContactPerson.CustomerUID into a
            //          join contactPerson in Models.tblContactPersons
            //          on a.FirstOrDefault().ContactPersonUID equals contactPerson.ContactPersonUID into contactperson
            //          join contactinfo in Models.tblContactInfoes
            //          on contactperson.FirstOrDefault().ContactInfoUID equals contactinfo.ContactInfoUID into info
            //          join contactaddress in Models.tblAddresses
            //          on contactperson.FirstOrDefault().AddressUID equals contactaddress.AddressUID into address
            //          select new CustomerListENT
            //          {
            //              Logo=customer.Logo_MediaUID,
            //              CustomerPerson = a.FirstOrDefault(),
            //              CustomerID=customer.CustomerID,
            //              Info=info.FirstOrDefault(),
            //              Address=address.FirstOrDefault()
            //          }
            //          ;
            #endregion
            var ret = from customer in Models.tblCustomers

                      //查询业务范围
                      join cust_bus in
                          (
                            from cust_busnature in Models.tblCust_BusNature
                            group cust_busnature by cust_busnature.CustomerUID into tempcust_bus
                            select new
                            {
                                CustomerID = tempcust_bus.FirstOrDefault().CustomerUID,
                                BusNatureUID = tempcust_bus.FirstOrDefault().BusNatureUID
                            }
                            )
                      on customer.CustomerUID equals cust_bus.CustomerID into cus_busnatureENT
                      from cust_bus in cus_busnatureENT.DefaultIfEmpty()

                      join nature in Models.mtblBusinessNatures
                      on cust_bus.BusNatureUID equals nature.BusNatureUID into BusNatureENT
                      from nature in BusNatureENT.DefaultIfEmpty()

                      //查询LOGO
                      join MediaRoom_Related in
                          (
                            from media_relate in Models.tblMediaRoom_Related
                            where media_relate.Category == "customer"
                            select media_relate
                           )
                       on customer.CustomerUID equals MediaRoom_Related.RelatedUID into tableMediaRoomRelated
                      from MediaRoom_Related in tableMediaRoomRelated.DefaultIfEmpty()

                      //join media in Models.tblMedias
                      //on MediaRoom_Related.MediaRoomUID equals media.MediaUID into TableMedia
                      //from media in TableMedia.DefaultIfEmpty()

                      //查询行业类别
                      join cust_procat in
                          (from cust_procat in Models.tblCust_ProdCat
                           group cust_procat by cust_procat.CustomerUID into temp_custprocat
                           select new
                           {
                               CatUID = temp_custprocat.FirstOrDefault().ProductCatUID,
                               CustomerUID = temp_custprocat.FirstOrDefault().CustomerUID
                           }
                           )
                      on customer.CustomerUID equals cust_procat.CustomerUID into Cust_ProCatENT
                      from cust_procat in Cust_ProCatENT.DefaultIfEmpty()

                      join subcat in
                          (from subcat in Models.mtblProductSubCats
                           group subcat by subcat.ProductCatUID into tempsubcat
                           select new
                           {
                               SubCatUID = tempsubcat.FirstOrDefault().ProductSubCatUID,
                               CatUID = tempsubcat.FirstOrDefault().ProductCatUID
                           }
                          )
                      on cust_procat.CatUID equals subcat.CatUID into SubCatENT
                      from subcat in SubCatENT.DefaultIfEmpty()

                      join procat_industry in
                          (from procat_industry in Models.mtblProdCat_Industry
                           group procat_industry by procat_industry.ProductSubCatUID into procat_industry
                           select new
                           {
                               SubUID = procat_industry.FirstOrDefault().ProductSubCatUID,
                               IndustryUID = procat_industry.FirstOrDefault().IndustryUID
                           })
                           on subcat.SubCatUID equals procat_industry.SubUID into ProCat_IndustryENT
                      from procat_industry in ProCat_IndustryENT.DefaultIfEmpty()

                      join industry in Models.mtblIndustries
                      on procat_industry.IndustryUID equals industry.IndustryUID into IndustryENT
                      from industry in IndustryENT.DefaultIfEmpty()

                      //查询联系人
                      join cust_ContactPerson in Models.tblCust_ContactPerson
                      on customer.CustomerUID equals cust_ContactPerson.CustomerUID into cust_ContactPersonENT
                      from cust_ContactPerson in cust_ContactPersonENT.DefaultIfEmpty()

                      join cp in Models.tblContactPersons
                      on cust_ContactPerson.ContactPersonUID equals cp.ContactPersonUID into contactpersonENT
                      from cp in contactpersonENT.DefaultIfEmpty()

                      //联系人基本信息
                      join info in Models.tblContactInfoes
                      on cp.ContactInfoUID equals info.ContactInfoUID into infoENT
                      from info in infoENT.DefaultIfEmpty()

                      //联系人地址
                      join address in Models.tblAddresses
                      on cp.AddressUID equals address.AddressUID into addressENT
                      from address in addressENT.DefaultIfEmpty()

                      //国家
                      join country in Models.mtblCountries
                      on address.CountryUID equals country.CountryUID into countryENT
                      from country in countryENT.DefaultIfEmpty()

                      //联系人称呼
                      join salutation in Models.mtblSalutations
                      on cp.SalutationUID equals salutation.SalutationUID into salutationENT
                      from salutation in salutationENT.DefaultIfEmpty()

                      //最后参加活动名称
                      join act_cp in
                          (
                            from act_Person in Models.tblAct_ContactPerson
                            group act_Person by act_Person.ContactPersonUID into tempact
                            select new
                            {
                                ACTID = tempact.FirstOrDefault().ActiviteUID,
                                CPID = tempact.FirstOrDefault().ContactPersonUID
                            }
                           )
                      on cp.ContactPersonUID equals act_cp.CPID into act_contactpersonENT
                      from tempact in act_contactpersonENT.DefaultIfEmpty()

                      join active in Models.tblActivites
                      on tempact.CPID equals active.ActiviteUID into ActiveENT
                      from active in ActiveENT.DefaultIfEmpty()

                      //联系人性质
                      join cp_attri in
                          (
                          from CPerson_Attribute in Models.tblCPerson_Attribute
                          group CPerson_Attribute by CPerson_Attribute.ContactPersonUID into tempattri
                          select new
                          {
                              ContactPersonUID = tempattri.FirstOrDefault().ContactPersonUID,
                              AttributeUID = tempattri.FirstOrDefault().AttributeUID
                          }
                          )
                      on cp.ContactPersonUID equals cp_attri.ContactPersonUID into CPerson_AttributeENT
                      from tempattri in CPerson_AttributeENT.DefaultIfEmpty()

                      join attribute in Models.mtblAttributes
                      on tempattri.AttributeUID equals attribute.AttributeUID into attributeENT
                      from attribute in attributeENT.DefaultIfEmpty()

                      select new CustomerListENT
                      {
                          CustomerID = customer.CustomerID,
                          CustomerUID = customer.CustomerUID,
                          CustomerName_Eng = customer.Name_Eng,
                          CustomerName_CN = customer.Name_Chi,
                          Prefix = salutation.Salutation_Eng,
                          ChristinaName = cp.ChristianName,
                          FirstName_Eng = cp.FirstName_Eng,
                          LastName_Eng = cp.LastName_Eng,
                          SubTitle = cp.OtherTitle_Eng,
                          Attribute = attribute.Attribute_Eng,
                          Position = cust_ContactPerson.Position_Eng,
                          FirstName_CN = cp.FirstName_Chi,
                          LastName_CN = cp.LastName_Chi,
                          Address = address.FullAdd_Eng,
                          Area = address.Province_Eng,
                          District = address.District_Eng,
                          City = address.City_Eng,
                          Country = country.Country_Eng,
                          //General Tel、Direct Tel、General Fax、Direct Fax暂不好实现
                          Email = info.Email1,
                          IsMember = customer.IsMember,
                          IsGCMember = customer.IsGCMember,
                          IsVip = customer.IsVIP,
                          BusinessNature=nature.BusNature_Eng,
                          IndustryCategory=industry.Industry_Eng,
                          LastJoindActivity = active.Activite_Name
                      }
                      ;
            //var sql = (ret as ObjectQuery<CustomerListENT>).ToTraceString();
            //var a = ret.ToList().Count();
            //return ret.ToList();
            //param.Count = ret.Count();
            //param.PageSize = int.MaxValue;
            ret = ret.OrderBy(strPropertyName + " " + strOrderBy).Skip(param.RecordStart).Take(param.PageSize);
            return ret.ToList();
        }



        public tblCustomer GetEntity(int UID)
        {
            return ctx.tblCustomers.Where(a=>a.CustomerUID==UID).SingleOrDefault();
        }

        public tblCustomer GetEntity(tblCustomer ent)
        {
            var result=ctx.tblCustomers.DefaultIfEmpty() ;
            if (ent.CustomerUID > 0)
                result = result.Where(a => a.CustomerUID == ent.CustomerUID);
            if (!string.IsNullOrEmpty(ent.CustomerID))
                result = result.Where(a => a.CustomerID == ent.CustomerID);
            return result.FirstOrDefault();
        }


        public List<tblCustomer> GetListByENT(tblCustomer ent, ref PageParam param, string strPropertyName, string strOrderBy)
        {
            var result = ctx.tblCustomers.Where(a => a.CustomerUID > 0);
            if (!string.IsNullOrEmpty(ent.CustomerID))
                result = result.Where(a => a.CustomerID == ent.CustomerID);
            if (!string.IsNullOrEmpty(ent.Name_Eng))
                result = result.Where(a => a.Name_Eng.Contains(ent.Name_Eng));
            if (!string.IsNullOrEmpty(ent.Name_Chi))
                result = result.Where(a => a.Name_Chi.Contains(ent.Name_Chi));
            param.Count = result.Count();
            result = result.OrderBy(strPropertyName + " " + strOrderBy).Skip(param.RecordStart).Take(param.PageSize);            
            return result.ToList();
        }

        public List<tblCustomer> GetListByENT(tblCustomer ent)
        {
            var result = ctx.tblCustomers.Where(a => a.CustomerUID > 0);
            if (!string.IsNullOrEmpty(ent.CustomerID))
                result = result.Where(a => a.CustomerID == ent.CustomerID);
            if (ent.Deleted >= 0)
                result = result.Where(a => a.Deleted == ent.Deleted);
            if (!string.IsNullOrEmpty(ent.Name_Eng))
                result = result.Where(a => a.Name_Eng.Contains(ent.Name_Eng));
            if (!string.IsNullOrEmpty(ent.Name_Chi))
                result = result.Where(a => a.Name_Chi.Contains(ent.Name_Chi));
            return result.ToList();
        }

        public int GetCountByENT(tblCustomer ent)
        {
            var result = ctx.tblCustomers.Where(a => a.CustomerUID > 0);
            if (!string.IsNullOrEmpty(ent.CustomerID))
                result = result.Where(a => a.CustomerID == ent.CustomerID);
            if (!string.IsNullOrEmpty(ent.Name_Eng))
                result = result.Where(a => a.Name_Eng.Contains(ent.Name_Eng));
            return result.Count();
        }

        public int InsertENT(tblCustomer ent)
        {
            ent.CreationDate = DateTime.Now;
            ent.LastModifyDate = DateTime.Now;
            ctx.tblCustomers.AddObject(ent);
            ctx.SaveChanges();
            ent = ctx.tblCustomers.Where(a => a.CustomerUID > 0).OrderBy(a => a.CustomerUID).ToList().Last();
            return ent.CustomerUID;
        }

        public int UpdateENT(tblCustomer ent)
        {
            var entCustomer= ctx.tblCustomers.Where(a=>a.CustomerUID==ent.CustomerUID).FirstOrDefault();
            if (entCustomer != null)
            {
                entCustomer.CustomerID = ent.CustomerID;
                entCustomer.CustomerSince = ent.CustomerSince;
                entCustomer.Name_Eng = ent.Name_Eng;
                entCustomer.Name_Chi = ent.Name_Chi;
                entCustomer.CustTypeUID = ent.CustTypeUID;
                entCustomer.EntryDate = ent.EntryDate;
                entCustomer.EstDate = ent.EstDate;
                entCustomer.BR = ent.BR;
                entCustomer.HKID_PassportNo = ent.HKID_PassportNo;
                entCustomer.NoOfEmp = ent.NoOfEmp;
                entCustomer.IsMember = ent.IsMember;
                entCustomer.IsGCMember = ent.IsGCMember;
                entCustomer.IsVIP = ent.IsVIP;
                entCustomer.HQLocatedinHK = ent.HQLocatedinHK;
                entCustomer.Terminated = ent.Terminated;
                entCustomer.Stroke = ent.Stroke;
                entCustomer.ContactInfoUID = ent.ContactInfoUID;
                entCustomer.AddressUID = ent.AddressUID;
                entCustomer.WebSite = ent.WebSite;
                entCustomer.ReferralBy = ent.ReferralBy;
                entCustomer.ReferralDate = ent.ReferralDate;
                entCustomer.Intro_Eng = ent.Intro_Eng;
                entCustomer.Intro_Chi = ent.Intro_Chi;
                entCustomer.Deleted = ent.Deleted;
                entCustomer.LastModifyDate = ent.LastModifyDate;
                entCustomer.LastModifyBy = ent.LastModifyBy;
                entCustomer.CreationDate = ent.CreationDate;
            }
            return ctx.SaveChanges();            
        }

        /// <summary>
        /// 更改字段Deleted字段值为1
        /// </summary>
        /// <param name="UID"></param>
        /// <returns></returns>
        public int UpdateDeleted(int UID)
        {
            var result = ctx.tblCustomers.Where(a => a.CustomerUID == UID).SingleOrDefault();
            if (result != null)
            {
                result.Deleted = 1;
            }
            return ctx.SaveChanges();
        }

        /// <summary>
        /// 物理删除一条记录
        /// </summary>
        /// <param name="UID"></param>
        /// <returns></returns>
        public int DeleteENT(int UID)
        {
            var result = ctx.tblCustomers.Where(a => a.CustomerUID == UID).SingleOrDefault();
            if (result != null)
            {
                ctx.tblCustomers.DeleteObject(result);
            }
            return ctx.SaveChanges();
        }

        /// <summary>
        /// 物理批量删除 若干条记录
        /// </summary>
        /// <param name="ent"></param>
        /// <returns></returns>
        public int DeleteENT(tblCustomer ent)
        {
            var temp = ctx.tblCustomers.Where(a => a.CustomerUID > 0);
            if (ent.CustomerUID > 0)
                temp = temp.Where(a => a.CustomerUID == ent.CustomerUID);
            if (!string.IsNullOrEmpty(ent.CustomerID))
                temp = temp.Where(a => a.CustomerID == ent.CustomerID);
            var list = temp.ToList();
            for (int i = 0; i < list.Count(); i++)
            {
                ctx.tblCustomers.DeleteObject(list[i]);
            }
            return ctx.SaveChanges();
        }

        public List<CustomerListENT> GetCustomerList(List<int> customeruidlist)
        {
            var ret = ctx.tblCustomers.Where(a=>customeruidlist.Contains(a.CustomerUID));
            return null;
        }
    }
}
